package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;


import com.BankMasterBean;
import com.ChangePasswordBean;
import com.CustomerMasterBean;
import com.CustomerMasterErrorBean;
import com.GeneralLedgerBean;
import com.JournalVoucherBean;
import com.PaymentVoucherBean;
import com.PurchaseUserBean;
import com.ReceiptsVoucherBean;
import com.RegisterBean;
import com.RegisterBean2;
import com.SalesUserBean;
import com.StockMasterBean;
import com.SupplierMasterBean;
import com.SupplierMasterErrorBean;
import com.UserBean;
import com.CreditNoteBean;
import com.DebitNoteBean;
import com.stockmbean;

import connection.DBConnection;



public class DBManipulation 
{
	static Statement st = null;
	static ResultSet rs = null;
	
	static String sd="Sundry Debtors";
	static String sc="Sundry Creditors";
	static DBConnection dbc=new DBConnection();	
	
	//insert method for customer master
	public static CustomerMasterBean insertCustomer(CustomerMasterBean cBean) 
	{
		Connection con=dbc.getNewConnection();
		
		String c1=cBean.getCustomerName();
		String c2=cBean.getCustomerCode();
		String c3=cBean.getCustomerAddress();
		String c4=cBean.getCustomerCity();
		String c5=cBean.getCustomerState();
		String c6=cBean.getCustomerTIN();
		String c7=cBean.getCustomerPAN();
		String c8=cBean.getCustomerMob();
		String c9=cBean.getCustomerEmail();
		String c10=cBean.getCustomerPerson();
		double c11=cBean.getCustomerBalance();
		
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf.format(now) ;
		
		
		//double c112=Double.parseDouble(c11);
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	//	String insertcust = "insert into CustomerMaster values('"+ cname + "'," + ccode+ ",'" + caddr + "','" + ccity + "','" + cstate + "','" + ctin + "','" + cpan + "','" + cmob + "','" + cemail + "','" + cper + "','" + cbal + "')";
		
		String insertcust = "insert into CustomerMaster(cname,ccode, caddr,ccity,cstate,ctin,cpan,cmob,cmail,cper,copen,status,datecreated)"+" values('"+ c1 + "','" + c2+ "','" + c3 + "','" + c4+ "','" + c5 + "','" + c6 + "','" + c7 + "','" + c8 + "','" + c9 + "','" + c10 + "','" + c11 + "','1','"+Datecreated+"')";
		try 
		{
			st.executeUpdate(insertcust);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return cBean;
	}	
	
	//insert method for supplier master
	public static SupplierMasterBean insertSupplier(SupplierMasterBean sBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		//String s1="",s2="",s3="",s4="",s5="",s6="",s7="",s8="",s9="",s10="",s11="";
		double s112=0.0;

		String s1=sBean.getSupplierName();
		String s2=sBean.getSupplierCode();
		String s3=sBean.getSupplierAddress();
		String s4=sBean.getSupplierCity();
		String s5=sBean.getSupplierState();
		String s6=sBean.getSupplierTIN();
		String s7=sBean.getSupplierPAN();
		String s8=sBean.getSupplierMob();
		String s9=sBean.getSupplierEmail();
		String s10=sBean.getSupplierPerson();
		double s11=sBean.getSupplierBalance();
		
		//s112=Double.parseDouble(s11);
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		//String insertsupp = "insert into SupplierMaster values('" + sname + "','" + scode+ "','" + saddr + "','" + scity + "','" + sstate + "','" + stin + "','" + span + "','" + smob + "','" + semail + "','" + sper + "','" + sball + "')";
				
		String insertsupp = "insert into SupplierMaster(scode,sname,saddr ,scity , sstate ,stin ,span ,smob , smail,sper,sopen,status,datecreated)" + "values('" + s2 + "','" + s1+ "','" + s3 + "','" + s4 + "','" + s5 + "','" + s6 + "','" + s7 + "','" + s8 + "','" + s9 + "','" + s10 + "','" + s11 + "','1','" + Datecreated + "')";
		try 
		{
			i = st.executeUpdate(insertsupp);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return sBean;
	}	
	
	// insert method for stock master
	public static StockMasterBean insertStock(StockMasterBean stBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		int st41=0;
		double st51=0.0;

		String st1=stBean.getStockItem();
		String st2=stBean.getStockGroup();
		String st3=stBean.getStockCode();
		String st4=stBean.getStockUnits();
		st41=Integer.parseInt(st4);
		
		String st5=stBean.getStockBalance();
		 st51=Double.parseDouble(st5);
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String insertstock = "insert into StockMaster(stname,stgroup,stcode,stunits,sopen)" + " values('" + st1 + "','" + st2+ "','" + st3 + "','" + st41 + "','" + st51 + "');";
				
		try 
		{
			i = st.executeUpdate(insertstock);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return stBean;
	}	


	
	
	// insert method for stock master inv
	public static stockmbean insertStockinv(stockmbean stBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		int st41=0;
		double st51=0.0;

		String st1=stBean.getStockItem();
		String st2=stBean.getStockGroup();
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String insertstock = "insert into StockMasterinv(smname,smgroup)" + " values('" + st1 + "','" + st2+ "');";
				
		try 
		{
			i = st.executeUpdate(insertstock);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return stBean;
	}	

	
	
	
	
	
	
	// insert method for bank master
	public static BankMasterBean insertBank(BankMasterBean bBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		

		String b1=bBean.getBankName();
		String b2=bBean.getBankAddress();
		String b3=bBean.getBankAccountType();
		String b4=bBean.getBankAccountNumber();
		String b5=bBean.getBankIFCS();
		String b6=bBean.getBankMICR();
		double b7=bBean.getBankBalance();
		 
		//double b71=Double.parseDouble(b7);
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		//String insertbank = "insert into BankMaster values('" + bname + "'," + baddr+ ",'" + bacctype + "','" + baccnum + "','" + bifcs + "','" + bmicr + "','" + bbal + "');";
		
		String insertbank = "insert into BankMaster(bname,baddr,btype,bnum,tfcs,micr,bopen,datecreated)"+ "values('" + b1 + "','" + b2+ "','" + b3 + "','" + b4 + "','" + b5 + "','" + b6 + "','" + b7 + "','" + Datecreated + "')";	
		try 
		{
			i = st.executeUpdate(insertbank);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return bBean;
	}	

	// insert method for general ledger
	public static GeneralLedgerBean insertGeneralLedger(GeneralLedgerBean glBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		double gl31=0.0;
		
		String gl1=glBean.getGeneralLedgerName();
		String gl2=glBean.getGeneralLedgerGroup();
		String gl3=glBean.getGeneralLedgerBalance();
		
		gl31=Double.parseDouble(gl3);
		
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf.format(now) ;

		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String insertgl = "insert into GeneralLedger2(gname,ggroup,gbal,datecreated)"+ "values('" + gl1+ "','" + gl2+ "','" + gl31 + "','" + Datecreated + "')";
				
		try 
		{
			i = st.executeUpdate(insertgl);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return glBean;
	}	
	
	
	// insert method for sales voucher
	public static SalesUserBean insertSalesVoucher(SalesUserBean salesbean) throws ParseException 
	{
		
		Connection con=dbc.getNewConnection();
		int i=0;
		
		//String s1=salesbean.getSalesOrderNumber(); 
		String s2=salesbean.getInvoiceNumber();
		
		String svdate=salesbean.getSalesDate();
		
		if(svdate.length()==2)
		{
			SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
		    String date1=current.format(new Date());

			String[] s1=date1.split("/");
			String day=s1[0];
			String month=s1[1];
			String year=s1[2];

		   svdate=svdate+"/"+month+"/"+year;
		}
		String oldDateString = svdate.toString();
		
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
		Date d = sdf.parse(oldDateString);
		sdf.applyPattern("yyyy-MM-dd");
		String s3 = sdf.format(d);
	
		String s4=salesbean.getCustomerName(); 
		String s5=salesbean.getCustomerCode(); 
		//String s6=salesbean.getCustomerInvoiceNumber(); 
		String s7=salesbean.getProductName(); 
		int s8=salesbean.getProductQuantity();		
		double s9=salesbean.getProductRate(); 
		double s10=salesbean.getNetAmount(); 
		double s11=salesbean.getTaxationCategory(); 
		double s12=salesbean.getTaxAmount(); 
		double s13=salesbean.getTransportCharges(); 
		double s14=salesbean.getTotalAmount(); 
		int s15=salesbean.getCreditLimit(); 
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		try 
		{
					st = con.createStatement();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
					String insertsales= "insert into SalesVoucher2(innum, date, scname ,ccode , stname , pquantity, prate , samount , staxcat, stamount, strans, stota, scredlim, sgroupname, status, datecreated, openingbalance)" + "values('"+s2+"','"+s3+"',(select cname from CustomerMaster where ccode='"+s4+"'),'"+ s5 +"','"+ s7 +"','"+ s8 +"','"+ s9 +"','"+s10 +"','"+ s11 +"','"+ s12 +"','"+ s13 +"','"+ s14 +"','"+ s15 +"','"+sd+"','1','"+Datecreated+"',(select copen from customermaster where ccode='"+s5+"') )";
					
		try
		{
			i = st.executeUpdate(insertsales);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		return salesbean;
				
			
	}

	// insert method for purchase voucher
	public static PurchaseUserBean insertPurchaseVoucher(PurchaseUserBean purchasebean) throws ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		  //String v1=purchasebean.getPurchaseOrderNumber();
		  String v2=purchasebean.getPurchaseInvoiceNumber();

		  String pvdate=purchasebean.getPurchaseDate();
		  
			if(pvdate.length()==2)
			{
				SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
			    String date1=current.format(new Date());

				String[] s1=date1.split("/");
				String day=s1[0];
				String month=s1[1];
				String year=s1[2];

			   pvdate=pvdate+"/"+month+"/"+year;
			}
					  
			String oldDateString = pvdate.toString();
			
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			Date d = sdf.parse(oldDateString);
			sdf.applyPattern("yyyy-MM-dd");
			String v3 = sdf.format(d);
			
		  String v4=purchasebean.getSupplierName();

		  String v5=purchasebean.getSupplierCode();
		  //String v6=purchasebean.getSupplierInvoiceNumber();
		  String v7=purchasebean.getProductName();
		  int v8=purchasebean.getPurchaseProdQuantity();
		  double v9=purchasebean.getPurchaseProdRate();
		  double v10=purchasebean.getPurchaseNetAmount();
		  double v11=purchasebean.getPurchaseTaxCategory();
		  double v12=purchasebean.getPurchaseTaxAmount();
		  double v13=purchasebean.getPurchaseTransport();
		  double v14=purchasebean.getPurchaseOtherCosts();
		  double v15=purchasebean.getPurchaseTotalAmount();
		  int v16=purchasebean.getPurchaseCreditLimit();
		  
		  java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	
		String insertpurchase= "insert into PurchaseVoucher2(pvinno , date , pvsname , scode , pvstname, pvqua, pvrate , pvneta , pvtaxcat ,pvtaxa , pvtrans , pvot, pvtota, pvcredlim, pgroupname, status,datecreated,openingbalance)" + "values('"+v2+"','"+v3+"',(select sname from SupplierMaster where scode='"+v4+"'),'"+ v5 +"','"+v7 +"','"+ v8 +"','"+ v9 +"','"+v10 +"','"+ v11 +"','"+ v12 +"','"+ v13 +"','"+ v14 +"','"+ v15 +"','"+ v16 +"','"+sc+"','1','"+Datecreated+"',(select sopen from suppliermaster where scode='"+v5+"'))";	
		try  
		{
			i = st.executeUpdate(insertpurchase);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return purchasebean;	
			
	}
	
	//insert method for payment voucher
	public static PaymentVoucherBean insertPaymentVoucher(PaymentVoucherBean pyBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		
		String pyy1=pyBean.getPaymentBankName();
		
		String py1="";
		String c="Cash";
		
		if(pyy1=="")
        {
        	py1=c;
        }
		else
		{
			py1=pyy1;
		}
		String py2=pyBean.getPaymentEntryNumber();
		String pydate=pyBean.getPaymentDate();
		
		if(pydate.length()==2)
		{
			SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
		    String date1=current.format(new Date());

			String[] s1=date1.split("/");
			String day=s1[0];
			String month=s1[1];
			String year=s1[2];

		   pydate=pydate+"/"+month+"/"+year;
		}
				
		String oldDateString = pydate.toString();
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
		Date d = sdf.parse(oldDateString);
		sdf.applyPattern("yyyy-MM-dd");
		String py3 = sdf.format(d);
		
		String py4=pyBean.getPaymentCheque();
		String py5=pyBean.getPaymentAccount();
		double py6=pyBean.getPaymentAmount();
		//double py61=Double.parseDouble(py6);
		//String py7=pyBean.getPaymentAccount2(); -
		//double py8=pyBean.getPaymentAmount2(); -
		//double py81=Double.parseDouble(py8);
		String py9=pyBean.getPaymentNarration();
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String insertpy = "insert into PaymentVoucher2(bname,pyenum,date,pycheque,pyaccname,pyamount,pynar,pyaccid,pygroupname,status,datecreated,openingbalance)"+ "values('" + py1 + "','" + py2 + "','" + py3 + "','" + py4 + "',(select cname from CustomerMaster where ccode='"+py5+"' union  select sname from SupplierMaster where scode='"+py5+"' union select gname from GeneralLedger2 where gcode='"+py5+"'),'" + py6 + "','" + py9 + "','" + py5 + "',(select case when ('"+py5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+py5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+py5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+py5+"') end as pygroupname),'1','"+Datecreated+"',(select copen from CustomerMaster where ccode='"+py5+"' union  select sopen from SupplierMaster where scode='"+py5+"' union select gbal from GeneralLedger2 where gcode='"+py5+"'));";
		
		try 
		{
			i = st.executeUpdate(insertpy);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return pyBean;
	}	
	
	//insert method receipts voucher
	public static ReceiptsVoucherBean insertReceiptsVoucher(ReceiptsVoucherBean rBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		//double r61=0.0, r81=0.0;
		
		String rr1=rBean.getReceiptsBankName();
	
		String r1="";
		String c="Cash";
		
		if(rr1=="")
        {
        	r1=c;
        }
		else
		{
			r1=rr1;
		}
		
		String r2=rBean.getReceiptsEntryNumber();
		String rvdate=rBean.getReceiptsDate();
		
		if(rvdate.length()==2)
		{
			SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
		    String date1=current.format(new Date());

			String[] s1=date1.split("/");
			String day=s1[0];
			String month=s1[1];
			String year=s1[2];

		   rvdate=rvdate+"/"+month+"/"+year;
		}
		String oldDateString = rvdate.toString();
		
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
		Date d = sdf.parse(oldDateString);
		sdf.applyPattern("yyyy-MM-dd");
		String r3 = sdf.format(d);
		
		String r4=rBean.getReceiptsCheque();
		
		String r5=rBean.getReceiptsAccount();
		
		double r6=rBean.getReceiptsAmount();
		//double r61=Double.parseDouble(r6);
		 
		//String r7=rBean.getReceiptsAccount2();
		
		//double r8=rBean.getReceiptsAmount2();
		//double r81=Double.parseDouble(r8);
		 
		String r9=rBean.getReceiptsNarration();
				
		 java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String insertr = "insert into ReceiptsVoucher2(bname,renum,date,rcheque,raccname,ramount,rnar,raccid,rgroupname,status,datecreated,openingbalance)"+ "values('" + r1 + "','" + r2 + "','" + r3 + "','" + r4 + "',(select cname from CustomerMaster where ccode='"+r5+"' union  select sname from SupplierMaster where scode='"+r5+"' union select gname from GeneralLedger2 where gcode='"+r5+"'),'" + r6 + "','" + r9 + "','" + r5 + "',(select case when ('"+r5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+r5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+r5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+r5+"') end as rgroupname),'1','"+Datecreated+"',(select copen from CustomerMaster where ccode='"+r5+"' union  select sopen from SupplierMaster where scode='"+r5+"' union select gbal from GeneralLedger2 where gcode='"+r5+"'))";
		
		//(select raccid, case when (raccid=(select ccode from customermaster)) then '"+sd+"' when (raccid=(select scode from suppliermaster)) then '"+sc+"' when (raccid=(select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode=raccid) end as rgroupname from receiptsvoucher2),(select racc2id, case when (racc2id=(select ccode from customermaster)) then '"+sd+"' when (racc2id=(select scode from suppliermaster)) then '"+sc+"' when (racc2id=(select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode=racc2id) end as rgroupname2 from receiptsvoucher2)		
		try 
		{
			i = st.executeUpdate(insertr);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return rBean;
	}	
	
	// insert method for journal voucher
	public static JournalVoucherBean insertJournalVoucher(JournalVoucherBean jBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		
		String j1=jBean.getJournalEntryNumber();
		String jvdate=jBean.getJournalDate();
		
		if(jvdate.length()==2)
		{
			SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
		    String date1=current.format(new Date());

			String[] s1=date1.split("/");
			String day=s1[0];
			String month=s1[1];
			String year=s1[2];

		   jvdate=jvdate+"/"+month+"/"+year;
		}
		
		String oldDateString = jvdate.toString();
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
		Date d = sdf.parse(oldDateString);
		sdf.applyPattern("yyyy-MM-dd");
		String j2 = sdf.format(d);
		
		String j3=jBean.getJournalAccount();
		
		double j4=jBean.getJournalAmount();
		//double j41=Double.parseDouble(j4);
		
		String j5=jBean.getJournalAccount2();
		double j6=jBean.getJournalAmount2();
		//double j61=Double.parseDouble(j6);
		
		String j7=jBean.getJournalNarration();
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		//(jname,date,daccdeb,jamount1,jacccred,jamount2,jnar)" + "
		
		String insertpy = "insert into JournalVoucher2(jname,date,jaccdeb,jamount1,jacccred,jamount2,jnar,jaccid,jacc2id,jgroupname,jgroupname2,status,datecreated,openingbalance1,openingbalance2)" + " values('" + j1+ "','" + j2 + "',(select cname from CustomerMaster where ccode='"+j3+"' union  select sname from SupplierMaster where scode='"+j3+"' union select gname from GeneralLedger2 where gcode='"+j3+"'),'" + j4 + "',(select cname from CustomerMaster where ccode='"+j5+"' union  select sname from SupplierMaster where scode='"+j5+"' union select gname from GeneralLedger2 where gcode='"+j5+"'),'" + j6 + "','" + j7 + "','" + j3 + "','" + j5 + "',(select case when ('"+j3+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+j3+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+j3+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+j3+"') end as rgroupname),(select case when ('"+j5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+j5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+j5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+j5+"') end as rgroupname2),'1','"+Datecreated+"',(select copen from CustomerMaster where ccode='"+j3+"' union  select sopen from SupplierMaster where scode='"+j3+"' union select gbal from GeneralLedger2 where gcode='"+j3+"'),(select copen from CustomerMaster where ccode='"+j5+"' union  select sopen from SupplierMaster where scode='"+j5+"' union select gbal from GeneralLedger2 where gcode='"+j5+"'))";
				
		try 
		{
			i = st.executeUpdate(insertpy);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return jBean;
	}	
	
	// insert method for Credit Note
	public static CreditNoteBean insertCreditNote(CreditNoteBean cnbean) throws ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		//String s1=salesbean.getSalesOrderNumber(); 
		String s2=cnbean.getInvoiceNumber(); 
		String cddate=cnbean.getDate(); 
		
		if(cddate.length()==2)
		{
			SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
		    String date1=current.format(new Date());

			String[] s1=date1.split("/");
			String day=s1[0];
			String month=s1[1];
			String year=s1[2];

		   cddate=cddate+"/"+month+"/"+year;
		}
				
		String oldDateString = cddate.toString();
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
		Date d = sdf.parse(oldDateString);
		sdf.applyPattern("yyyy-MM-dd");
		String s3 = sdf.format(d);
		
		String s4=cnbean.getCustomerName(); 
		String s5=cnbean.getCustomerCode(); 
		//String s6=cnbean.getCustomerInvoiceNumber(); 
		String s7=cnbean.getProductName(); 
		int s8=cnbean.getProductQuantity();		
		double s9=cnbean.getProductRate(); 
		double s10=cnbean.getNetAmount(); 
		double s11=cnbean.getTaxationCategory(); 
		double s12=cnbean.getTaxAmount(); 
		double s13=cnbean.getTransportCharges(); 
		double s14=cnbean.getTotalAmount(); 
		String s15=cnbean.getSalesRejectionReason();
		int s16=cnbean.getCreditLimit(); 
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	
		String insertcreditnote= "insert into CreditNote(cdinno, date, cdcname ,ccode ,cdstname , cdquantity, cdrate ,cdnet , cdtaxcat, cdtax, cdtrans, cdtotal, cdrejection, cdcredlim, cdgroupname,status,datecreated,openingbalance)" + "values('"+s2+"','"+s3+"',(select cname from CustomerMaster where ccode='"+s4+"'),'"+ s4 +"','"+ s7 +"','"+ s8 +"','"+ s9 +"','"+s10 +"','"+ s11 +"','"+ s12 +"','"+ s13 +"','"+ s14 +"','"+ s15 +"','"+ s16 +"','"+sd+"','1','"+Datecreated+"',(select copen from customermaster where ccode='"+s5+"'))";	
		try 
		{
			i = st.executeUpdate(insertcreditnote);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return cnbean;		
			
	}
	
	// insert method for Debit Note
	public static DebitNoteBean insertDebitNote(DebitNoteBean dnbean) throws NullPointerException, ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		  //String v1=purchasebean.getPurchaseOrderNumber(); 
		  String v2=dnbean.getPurchaseInvoiceNumber();
		  String dbdate=dnbean.getPurchaseDate();
		  
			if(dbdate.length()==2)
			{
				SimpleDateFormat current = new SimpleDateFormat("dd/MM/yyyy");
			    String date1=current.format(new Date());

				String[] s1=date1.split("/");
				String day=s1[0];
				String month=s1[1];
				String year=s1[2];

			   dbdate=dbdate+"/"+month+"/"+year;
			}
		  
			String oldDateString = dbdate.toString();
			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
			Date d = sdf.parse(oldDateString);
			sdf.applyPattern("yyyy-MM-dd");
			String v3 = sdf.format(d);
			
		  String v4=dnbean.getSupplierName();
		  String v5=dnbean.getSupplierCode();
		  //String v6=purchasebean.getSupplierInvoiceNumber();
		  String v7=dnbean.getProductName();
		  int v8=dnbean.getPurchaseProdQuantity();
		  double v9=dnbean.getPurchaseProdRate();
		  double v10=dnbean.getPurchaseNetAmount();
		  double v11=dnbean.getPurchaseTaxCategory();
		  double v12=dnbean.getPurchaseTaxAmount();
		  double v13=dnbean.getPurchaseTransport();
		  double v14=dnbean.getPurchaseOtherCosts();
		  double v15=dnbean.getPurchaseTotalAmount();
		  String v16=dnbean.getPurchaseRejectionReason();
		  int v17=dnbean.getPurchaseCreditLimit();
		
		  java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Datecreated = sdf1.format(now) ;
		
		  
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		
		String insertdebitnote= "insert into DebitNote(dbinno ,date ,dbsname ,scode ,dbstname ,dbquantity , dbrate , dbnet , dbtaxcat ,dbtax , dbtrans, dbot, dbtotal ,dbrejection , dbcredlim, dbgroupname, status, datecreated, openingbalance)" + "values('"+v2+"','"+v3+"',(select sname from SupplierMaster where scode='"+v4+"'),'"+ v5 +"','"+v7 +"','"+ v8 +"','"+ v9 +"','"+v10 +"','"+ v11 +"','"+ v12 +"','"+ v13 +"','"+ v14 +"','"+ v15 +"','"+ v16 +"','"+ v17 +"','"+sc+"','1','"+Datecreated+"',(select sopen from suppliermaster where scode='"+v5+"'))";	
		try 
		{
			i = st.executeUpdate(insertdebitnote);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return dnbean;	
			
	}
	
	
	
	//update method for customer master
	public static CustomerMasterBean updateCustomer(CustomerMasterBean cBean) 
	{
		Connection con=dbc.getNewConnection();
		
		String c1=cBean.getCustomerName();
		String c2=cBean.getCustomerCode();
		String c3=cBean.getCustomerAddress();
		String c4=cBean.getCustomerCity();
		String c5=cBean.getCustomerState();
		String c6=cBean.getCustomerTIN();
		String c7=cBean.getCustomerPAN();
		String c8=cBean.getCustomerMob();
		String c9=cBean.getCustomerEmail();
		String c10=cBean.getCustomerPerson();
		double c11=cBean.getCustomerBalance();
		
		//double c112=Double.parseDouble(c11);
		try 
		{
			st = con.createStatement();
			String updatecust = "update CustomerMaster set cname='"+ c1 + "', caddr='"+ c3 + "',ccity='"+ c4 + "',cstate='"+ c5 + "',ctin='"+ c6 + "',cpan='"+ c7 + "',cmob='"+ c8 + "',cmail='"+ c9 + "',cper='"+ c10 + "',copen='"+ c11 + "' where ccode='"+c2+"' ";
			st.executeUpdate(updatecust);
			
			String up1="update SalesVoucher2 set scname='"+c1+"' where ccode='"+c2+"'  ";
	    	st.executeUpdate(up1);
	    	   
	    	String up2="update PaymentVoucher2 set pyaccname='"+c1+"' where pyaccid='"+c2+"' ";
	    	st.executeUpdate(up2); 

   	        String up3="update JournalVoucher2 set jaccdeb='"+c1+"' where jaccid='"+c2+"' ";
	    	st.executeUpdate(up3);

	    	String up31="update JournalVoucher2 set jacccred='"+c1+"' where jaccid='"+c2+"' ";
	    	st.executeUpdate(up31);
	    	  
	    	String up4="update ReceiptsVoucher2 set raccname='"+c1+"' where raccid='"+c2+"' ";
	    	st.executeUpdate(up4);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return cBean;
	}	
	
	//update method for supplier master
	public static SupplierMasterBean updateSupplier(SupplierMasterBean sBean) 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;

		double s112=0.0;

		String s1=sBean.getSupplierName();
		String s2=sBean.getSupplierCode();
		String s3=sBean.getSupplierAddress();
		String s4=sBean.getSupplierCity();
		String s5=sBean.getSupplierState();
		String s6=sBean.getSupplierTIN();
		String s7=sBean.getSupplierPAN();
		String s8=sBean.getSupplierMob();
		String s9=sBean.getSupplierEmail();
		String s10=sBean.getSupplierPerson();
		double s11=sBean.getSupplierBalance();
		
		//s112=Double.parseDouble(s11);
		
		try 
		{
			st = con.createStatement();
			String updatesupp = "update SupplierMaster set sname='" + s1+ "',saddr='" + s3+ "' ,scity='" + s4+ "' , sstate='" + s5+ "',stin='" + s6+ "' ,span='" + s7+ "' ,smob='" + s8+ "' , smail='" + s9+ "',sper='" + s10+ "',sopen='" + s11+ "' where scode='"+s2+"' " ;
			st.executeUpdate(updatesupp);
			
			String up1="update PurchaseVoucher2 set pvsname='"+s1+"' where scode='"+s2+"'  ";
	    	st.executeUpdate(up1);
	    	   
	    	String up2="update PaymentVoucher2 set pyaccname='"+s1+"' where pyaccid='"+s2+"' ";
	    	st.executeUpdate(up2); 

   	        String up3="update JournalVoucher2 set jaccdeb='"+s1+"' where jaccid='"+s2+"' ";
	    	st.executeUpdate(up3);

	    	String up31="update JournalVoucher2 set jacccred='"+s1+"' where jaccid='"+s2+"' ";
	    	st.executeUpdate(up31);
	    	  
	    	String up4="update ReceiptsVoucher2 set raccname='"+s1+"' where raccid='"+s2+"' ";
	    	st.executeUpdate(up4);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return sBean;
	}

	// update method for sales voucher
	public static SalesUserBean updateSalesVoucher(SalesUserBean sales2bean) throws ParseException
	{
		Connection con=dbc.getNewConnection();
		
		int i=0;
		
		//String s1=sales2bean.getSalesOrderNumber(); 
		String s2=sales2bean.getInvoiceNumber(); 
		String svdate=sales2bean.getSalesDate(); 
		     
		 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
		 String s3 =tosql.format(formatter.parse(svdate));
		
		String s4=sales2bean.getCustomerName(); 
		String s5=sales2bean.getCustomerCode(); 
		//String s6=sales2bean.getCustomerInvoiceNumber(); 
		String s7=sales2bean.getProductName(); 
		int s8=sales2bean.getProductQuantity();		
		double s9=sales2bean.getProductRate(); 
		double s10=sales2bean.getNetAmount(); 
		double s11=sales2bean.getTaxationCategory(); 
		double s12=sales2bean.getTaxAmount(); 
		double s13=sales2bean.getTransportCharges(); 
		double s14=sales2bean.getTotalAmount(); 
		int s15=sales2bean.getCreditLimit(); 
		
		java.util.Date now = new java.util.Date();
		  String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
		  SimpleDateFormat sdf1 = new SimpleDateFormat(DATE_FORMAT);
		  
	    String Dateedited = sdf1.format(now) ;
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String updatesales= "update SalesVoucher2 set date='"+s3+"', scname='"+s4+"', ccode='"+s5+"', stname='"+s7+"' ,pquantity='"+s8+"', prate='"+s9+"', samount='"+s10+"', staxcat='"+s11+"', stamount='"+s12+"', strans='"+s13+"', stota='"+s14+"', scredlim='"+s15+"',sgroupname='"+sd+"', dateedited='"+Dateedited+"' where innum='"+s2+"'; ";	
		try 
		{
			i = st.executeUpdate(updatesales);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return sales2bean;		
	}

	// update method for purchase voucher
	public static PurchaseUserBean updatePurchaseVoucher(PurchaseUserBean purchasebean) throws ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		  //String v1=purchasebean.getPurchaseOrderNumber();
		  String v2=purchasebean.getPurchaseInvoiceNumber();

		  String pvdate=purchasebean.getPurchaseDate();
		  
			 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
			 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
			 String v3 =tosql.format(formatter.parse(pvdate));
			
		  String v4=purchasebean.getSupplierName();

		  String v5=purchasebean.getSupplierCode();
		  //String v6=purchasebean.getSupplierInvoiceNumber();
		  String v7=purchasebean.getProductName();
		  int v8=purchasebean.getPurchaseProdQuantity();
		  double v9=purchasebean.getPurchaseProdRate();
		  double v10=purchasebean.getPurchaseNetAmount();
		  double v11=purchasebean.getPurchaseTaxCategory();
		  double v12=purchasebean.getPurchaseTaxAmount();
		  double v13=purchasebean.getPurchaseTransport();
		  double v14=purchasebean.getPurchaseOtherCosts();
		  double v15=purchasebean.getPurchaseTotalAmount();
		  int v16=purchasebean.getPurchaseCreditLimit();
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	
		String updatepurchase= "update PurchaseVoucher2 set date='"+v3+"', pvsname='"+v4+"', scode='"+v5+"', pvstname='"+v7+"', pvqua='"+v8+"', pvrate='"+v9+"', pvneta='"+v10+"', pvtaxcat='"+v11+"', pvtaxa='"+v12+"', pvtrans='"+v13+"', pvot='"+v14+"', pvtota='"+v15+"', pvcredlim='"+v16+"',pgroupname='"+sc+"' where pvinno='"+v2+"';";	
		try 
		{
			i = st.executeUpdate(updatepurchase);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return purchasebean;	
			
	}

	
	//update method for payment voucher
	public static PaymentVoucherBean updatePaymentVoucher(PaymentVoucherBean pyBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		
		String py1=pyBean.getPaymentBankName();
		String py2=pyBean.getPaymentEntryNumber();
		String pydate=pyBean.getPaymentDate();
		
		 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
		 String py3 =tosql.format(formatter.parse(pydate));
		
		String py4=pyBean.getPaymentCheque();
		String py5=pyBean.getPaymentAccount();
		double py6=pyBean.getPaymentAmount();
		//String py7=pyBean.getPaymentAccount2();
		//double py8=pyBean.getPaymentAmount2();
		String py9=pyBean.getPaymentNarration();

		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		String updatepy = "update PaymentVoucher2 set bname='" + py1 + "',date='" + py3 + "',pycheque='" + py4 + "',pyaccname='" + py5 + "',pyamount='" + py6 + "',pynar='" + py9 + "',pyaccid=(select ccode from CustomerMaster where cname='"+py5+"' union  select scode from SupplierMaster where sname='"+py5+"' union select gcode from GeneralLedger2 where gname='"+py5+"'), pygroupname=(select case when ('"+py5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+py5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+py5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+py5+"') end as pygroupname) where pyenum='"+py2+"';";
		
		try 
		{
			i = st.executeUpdate(updatepy);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return pyBean;
	}	
	
	//update method receipts voucher
	public static ReceiptsVoucherBean updateReceiptsVoucher(ReceiptsVoucherBean rBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		//double r61=0.0, r81=0.0;
		
		String r1=rBean.getReceiptsBankName();
		String r2=rBean.getReceiptsEntryNumber();
		String rvdate=rBean.getReceiptsDate();
		
		 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
		 String r3 =tosql.format(formatter.parse(rvdate));
		
		String r4=rBean.getReceiptsCheque();
		
		String r5=rBean.getReceiptsAccount();
		
		double r6=rBean.getReceiptsAmount();
		//double r61=Double.parseDouble(r6);
		 
		//String r7=rBean.getReceiptsAccount2();
		
		//double r8=rBean.getReceiptsAmount2();
		//double r81=Double.parseDouble(r8);
		 
		String r9=rBean.getReceiptsNarration();
				
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
	
		String updater="update ReceiptsVoucher2 set bname='" + r1 + "',date='" + r3 + "',rcheque='" + r4 + "',raccname='" + r5 + "',ramount='" + r6 + "',rnar='" + r9 + "',raccid=(select ccode from CustomerMaster where cname='"+r5+"' union  select scode from SupplierMaster where sname='"+r5+"' union select gcode from GeneralLedger2 where gname='"+r5+"'), rgroupname=(select case when ('"+r5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+r5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+r5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+r5+"') end as pygroupname) where renum='"+r2+"' ;";
		//(select raccid, case when (raccid=(select ccode from customermaster)) then '"+sd+"' when (raccid=(select scode from suppliermaster)) then '"+sc+"' when (raccid=(select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode=raccid) end as rgroupname from receiptsvoucher2),(select racc2id, case when (racc2id=(select ccode from customermaster)) then '"+sd+"' when (racc2id=(select scode from suppliermaster)) then '"+sc+"' when (racc2id=(select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode=racc2id) end as rgroupname2 from receiptsvoucher2)		
		try 
		{
			i = st.executeUpdate(updater);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return rBean;
	}	
	
	// update method for journal voucher
	public static JournalVoucherBean updateJournalVoucher(JournalVoucherBean jBean) throws ParseException 
	{
		Connection con=dbc.getNewConnection();
		
		int i = 0;
		
		String j1=jBean.getJournalEntryNumber();
		String jvdate=jBean.getJournalDate();
		
		 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
		 String j2 =tosql.format(formatter.parse(jvdate));
		
		String j3=jBean.getJournalAccount();
		
		double j4=jBean.getJournalAmount();
		//double j41=Double.parseDouble(j4);
		
		String j5=jBean.getJournalAccount2();
		double j6=jBean.getJournalAmount2();
		//double j61=Double.parseDouble(j6);
		
		String j7=jBean.getJournalNarration();
		
		try 
		{
			st = con.createStatement();
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		//(jname,date,daccdeb,jamount1,jacccred,jamount2,jnar)" + "
		
		String updatejv = "update JournalVoucher2 set date='" + j2 + "',jaccdeb='" + j3 + "',jamount1='" + j4 + "',jacccred='" + j5 + "',jamount2='" + j6 + "',jnar='" + j7 + "',jaccid=(select ccode from CustomerMaster where cname='"+j3+"' union  select scode from SupplierMaster where sname='"+j3+"' union select gcode from GeneralLedger2 where gname='"+j3+"'),jacc2id=(select ccode from CustomerMaster where cname='"+j5+"' union  select scode from SupplierMaster where sname='"+j5+"' union select gcode from GeneralLedger2 where gname='"+j5+"'),jgroupname=(select case when ('"+j3+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+j3+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+j3+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+j3+"') end as rgroupname), jgroupname2=(select case when ('"+j5+"'=ANY (select ccode from customermaster)) then '"+sd+"' when ('"+j5+"'=ANY (select scode from suppliermaster)) then '"+sc+"' when ('"+j5+"'=ANY (select gcode from generalledger2)) then (select ggroup from generalledger2 where gcode='"+j5+"') end as rgroupname2) where jname='"+j1+"' ";
				
		try 
		{
			i = st.executeUpdate(updatejv);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return jBean;
	}	


	// update method for Credit Note
	public static CreditNoteBean updateCreditNote(CreditNoteBean cnbean) throws ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		//String s1=salesbean.getSalesOrderNumber(); 
		String s2=cnbean.getInvoiceNumber(); 
		String cddate=cnbean.getDate(); 
		
		 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
		 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
		 String s3 =tosql.format(formatter.parse(cddate));
		
		String s4=cnbean.getCustomerName(); 
		String s5=cnbean.getCustomerCode(); 
		//String s6=cnbean.getCustomerInvoiceNumber(); 
		String s7=cnbean.getProductName(); 
		int s8=cnbean.getProductQuantity();		
		double s9=cnbean.getProductRate(); 
		double s10=cnbean.getNetAmount(); 
		double s11=cnbean.getTaxationCategory(); 
		double s12=cnbean.getTaxAmount(); 
		double s13=cnbean.getTransportCharges(); 
		double s14=cnbean.getTotalAmount(); 
		String s15=cnbean.getSalesRejectionReason();
		int s16=cnbean.getCreditLimit(); 

		String updatecreditnote= "update CreditNote set date='"+s3+"', cdcname='"+s4+"' ,ccode='"+s5+"' ,cdstname='"+s7+"' , cdquantity='"+s8+"', cdrate='"+s9+"' ,cdnet='"+s10+"' , cdtaxcat='"+s11+"', cdtax='"+s12+"', cdtrans='"+s13+"', cdtotal='"+s14+"', cdrejection='"+s15+"', cdcredlim='"+s16+"', cdgroupname='"+sd+"' where cdinno='"+s2+"' ";
		try 
		{
			st = con.createStatement();
			i = st.executeUpdate(updatecreditnote);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return cnbean;		
			
	}
	
	// update method for Debit Note
	public static DebitNoteBean updateDebitNote(DebitNoteBean dnbean) throws NullPointerException, ParseException
	{
		Connection con=dbc.getNewConnection();
		int i=0;
		
		  //String v1=purchasebean.getPurchaseOrderNumber(); 
		  String v2=dnbean.getPurchaseInvoiceNumber();
		  String dbdate=dnbean.getPurchaseDate();
		  
			 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
			 SimpleDateFormat tosql = new SimpleDateFormat("yyyy-MM-dd");
			 String v3 =tosql.format(formatter.parse(dbdate));
			
		  String v4=dnbean.getSupplierName();
		  String v5=dnbean.getSupplierCode();
		  //String v6=purchasebean.getSupplierInvoiceNumber();
		  String v7=dnbean.getProductName();
		  int v8=dnbean.getPurchaseProdQuantity();
		  double v9=dnbean.getPurchaseProdRate();
		  double v10=dnbean.getPurchaseNetAmount();
		  double v11=dnbean.getPurchaseTaxCategory();
		  double v12=dnbean.getPurchaseTaxAmount();
		  double v13=dnbean.getPurchaseTransport();
		  double v14=dnbean.getPurchaseOtherCosts();
		  double v15=dnbean.getPurchaseTotalAmount();
		  String v16=dnbean.getPurchaseRejectionReason();
		  int v17=dnbean.getPurchaseCreditLimit();

			String updatedebitnote= " update DebitNote set date='"+v3+"' ,dbsname='"+v4+"' ,scode='"+v5+"' ,dbstname='"+v7+"' ,dbquantity='"+v8+"' , dbrate='"+v9+"' , dbnet='"+v10+"' , dbtaxcat='"+v11+"' ,dbtax='"+v12+"' , dbtrans='"+v13+"', dbot='"+v14+"', dbtotal='"+v15+"' ,dbrejection='"+v16+"' ,dbcredlim='"+v17+"', dbgroupname='"+sc+"' where dbinno='"+v2+"' ";

		try 
		{
			st = con.createStatement();
			i = st.executeUpdate(updatedebitnote);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return dnbean;	
			
	}

	
	


	//delete method from customer master
	public static List<String> deleteCustomerMaster(String CodeNumber)
	{
		String CodeNumber1=CodeNumber;
		
		Connection con=dbc.getNewConnection();
		
		//CustomerMasterErrorBean CME=new CustomerMasterErrorBean();
		
		List<String> CustomerRecords=new ArrayList<String>();	
		
		//int i=0;
				
		String check="select vouchertype,invoice,name,amount from( ( select 'Sales Voucher' as vouchertype, innum as invoice,scname as name, stota as amount from SalesVoucher2 where ccode='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Credit note' as vouchertype, cdinno as invoice,cdcname as name, cdtotal as amount from CreditNote where ccode='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Receipts Voucher' as vouchertype, renum as invoice,raccname as name, ramount as amount  from ReceiptsVoucher2 where raccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Payment Voucher' as vouchertype, pyenum as invoice, pyaccname as name, pyamount as amount from paymentvoucher2 where pyaccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Journal Voucher' as vouchertype, jname as invoice, jaccdeb as name, jamount1 as amount  from JournalVoucher2 where jaccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Journal Voucher' as vouchertype, jname as invoice, jacccred as name, jamount2 as amount from JournalVoucher2 where jacc2id='"+CodeNumber1+"' AND status=1))a ";
		try
		{
			st=con.createStatement();
			rs=st.executeQuery(check);
			while(rs.next())
			{
				String v1=rs.getString(1);
				String v2=rs.getString(2);
				String v3=rs.getString(3);
				String v4=rs.getString(3);
							
				CustomerRecords.add(v1);
				CustomerRecords.add(v2);
				CustomerRecords.add(v3);
				CustomerRecords.add(v4);
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		if(CustomerRecords.size()==0)
		{	
						
			System.out.println(CustomerRecords);
			String deletecustomer= "update CustomerMaster set status=0 where ccode='"+CodeNumber1+"' ";
			try
			{
				st=con.createStatement();
				st.executeUpdate(deletecustomer);
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return CustomerRecords;
	}
	
	//delete method from supplier master
	public static List<String> deleteSupplierMaster(String CodeNumber)
	{
		String CodeNumber1=CodeNumber;
		
		Connection con=dbc.getNewConnection();
		
		//SupplierMasterErrorBean SME=new SupplierMasterErrorBean();
		
		List<String> SupplierRecords=new ArrayList<String>();	
		
		//int i=0;
				
		String check="select vouchertype,invoice,name,amount from( ( select 'Purchase Voucher' as vouchertype, pvinno as invoice, pvsname as name, pvtota as amount from PurchaseVoucher2 where scode='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Debit note' as vouchertype, dbinno as invoice, dbsname as name, dbtotal as amount from DebitNote where scode='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Receipts Voucher' as vouchertype, renum as invoice,raccname as name, ramount as amount  from ReceiptsVoucher2 where raccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Payment Voucher' as vouchertype, pyenum as invoice, pyaccname as name, pyamount as amount from paymentvoucher2 where pyaccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Journal Voucher' as vouchertype, jname as invoice, jaccdeb as name, jamount1 as amount  from JournalVoucher2 where jaccid='"+CodeNumber1+"' AND status=1) UNION ALL (select 'Journal Voucher' as vouchertype, jname as invoice, jacccred as name, jamount2 as amount from JournalVoucher2 where jacc2id='"+CodeNumber1+"' AND status=1))a ";
		try
		{
			st=con.createStatement();
			rs=st.executeQuery(check);
			while(rs.next())
			{
				String v1=rs.getString(1);
				String v2=rs.getString(2);
				String v3=rs.getString(3);
				String v4=rs.getString(3);
							
				SupplierRecords.add(v1);
				SupplierRecords.add(v2);
				SupplierRecords.add(v3);
				SupplierRecords.add(v4);
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		if(SupplierRecords.size()==0)
		{	
						
			String deleteSupplier= "update SupplierMaster set status=0 where scode='"+CodeNumber1+"' ";
			try
			{
				st=con.createStatement();
				st.executeUpdate(deleteSupplier);
			
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return SupplierRecords;
	}



	// delete method for sales voucher
	public static void deleteSalesVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletesales= "update Salesvoucher2 set status='0' where innum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletesales);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}

	
	// delete method for purchase voucher
	public static void deletePurchaseVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletepurchase= "update PurchaseVoucher2 set status='0' where pvinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletepurchase);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// delete method for receipts voucher
	public static void deleteReceiptsVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletereceipts= "update ReceiptsVoucher2 set status='0' where renum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletereceipts);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// delete method for payment voucher
	public static void deletePaymentVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletepayment= "update PaymentVoucher2 set status='0' where pyenum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletepayment);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// delete method for journal voucher
	public static void deleteJournalVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletejournal= "update JournalVoucher2 set status='0' where jname='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletejournal);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	
	// delete method for debit note
	public static void deleteDebitNote(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletedebit= "update DebitNote set status='0' where dbinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletedebit);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}

	
	// delete method for credit note
	public static void deleteCreditNote(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String deletecredit= "update CreditNote set status='0' where cdinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(deletecredit);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}	
	

	// restore method for customer master
	public static void restoreCustomerMaster(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorecustomer= "update CustomerMaster set status='1' where ccode='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorecustomer);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}	

	
	// restore method for supplier master
	public static void restoreSupplierMaster(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restoresupplier= "update SupplierMaster set status='1' where scode='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restoresupplier);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}	

	
	// check ledger name 
	public static void checkLedger(String LedgerName)
	{
		String LedgerName1=LedgerName;
		
		Connection con=dbc.getNewConnection();

		String sql= "select exists(select ccode from customermaster where ccode='"+LedgerName1+"') as a;";
		try 
		{
			st = con.createStatement();
			int i=st.executeUpdate(sql);
			
			if(i==1)
			{
				
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	


	// restore method for sales voucher
	public static void restoreSalesVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restoresales= "update Salesvoucher2 set status='1' where innum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restoresales);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}

	
	// restore method for purchase voucher
	public static void restorePurchaseVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorepurchase= "update PurchaseVoucher2 set status='1' where pvinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorepurchase);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// restore method for receipts voucher
	public static void restoreReceiptsVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorereceipts= "update ReceiptsVoucher2 set status='1' where renum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorereceipts);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// restore method for payment voucher
	public static void restorePaymentVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorepayment= "update PaymentVoucher2 set status='1' where pyenum='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorepayment);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	// restore method for journal voucher
	public static void restoreJournalVoucher(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorejournal= "update JournalVoucher2 set status='1' where jname='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorejournal);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}
	
	
	// restore method for debit note
	public static void restoreDebitNote(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restoredebit= "update DebitNote set status='1' where dbinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restoredebit);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}

	
	// restore method for credit note
	public static void restoreCreditNote(String VoucherNumber)
	{
		String VoucherNumber1=VoucherNumber;
		
		Connection con=dbc.getNewConnection();

		String restorecredit= "update CreditNote set status='1' where cdinno='"+VoucherNumber1+"' ";
		try 
		{
			st = con.createStatement();
			st.executeUpdate(restorecredit);
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}					
	}	

	
	// update receipts bank date
	public static void updateReceiptsBankDate(String bankdate[])
	{
		
	}
	
	// update receipts payment date
	public static void updatePaymentBankDate(String bankdate[])
	{
		
	}

	
	public static RegisterBean insertRegisterInfo(RegisterBean rbean) 
    {
        Connection con=dbc.getNewConnection();
        
        
        int i=0;
        
        
        String r1=rbean.getPan();
        String r2=rbean.getDateofBirth();
        String r3=rbean.getPassword();
        String r4=rbean.getEmail();
        long r5=rbean.getPhoneno();
        
        long a=0;
        
        
        try 
        {
            st = con.createStatement();
            String insertreg = "insert into register(panno,dob,pass,email,mobile)" + " values('" + r1+ "','" + r2 + "','" + r3 + "','" + r4 + "','" + r5 + "')";
            i = st.executeUpdate(insertreg);
        }
        catch (SQLException e) 
        {
            e.printStackTrace();
        }

        

                
        try 
        {
   
            String insertreg1 = "insert into register2(panno)" + "values('"+r1+"')";
            st.executeUpdate(insertreg1);  
        }
        catch (SQLException e) 
        {
            e.printStackTrace();
        }  
        
        
        return rbean;
    }
    
    public static RegisterBean2 insertRegisterInfo2(RegisterBean2 rbean) 
    {
        Connection con=dbc.getNewConnection();
        
        
        int i=0;
        String r0=rbean.getPan();
        String r1=rbean.getNameofOrganisation();
        String r2=rbean.getMvatNo();
        String r3=rbean.getAddress();
        
        String r4=rbean.getCity();
        String r5=rbean.getDistrict();
        String r6=rbean.getState();
        String r7=rbean.getPin();
        String r8=rbean.getPerson();
        String r9=rbean.getDesignation();
        try 
        {
            st = con.createStatement();
        }
        catch (SQLException e) 
        {
            e.printStackTrace();
        }

        
        String insertreg = "insert into register2(panno,org_name,mvat,address,city,dist,state,pin,autho_person,designation)" + " values('" + r0+ "','" + r1+ "','" + r2 + "','" + r3 + "','" + r4 + "','" + r5 + "','" + r6 + "','" + r7 + "','" + r8 + "','" + r9 + "')";
                
        try 
        {
            i = st.executeUpdate(insertreg);    
        }
        catch (SQLException e) 
        {
            e.printStackTrace();
        }  
        
        
        return rbean;
    }
    
    
	public static ChangePasswordBean changepassword(ChangePasswordBean cpbean) 
	{
		Connection con=dbc.getNewConnection();
		
		String pn=cpbean.getPan();
		String op=cpbean.getOldPassword();
		String p=cpbean.getPassword();
		
		String pan1=null,pass1=null;
		
		try 
		{
			st = con.createStatement();
			ResultSet rs = st.executeQuery("select panno,pass from register where panno= '"+ pn + "'");

			if (rs.next()) 
			{ 	
				 pan1 = rs.getString("panno");
				 pass1 = rs.getString("pass");
			}
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}

		String insertreg = "UPDATE register SET pass = '"+p+"' WHERE panno ='"+pn+"' and pass ='"+op+"'";
				
		try 
		{
			
		
			int i = st.executeUpdate(insertreg);
			
			 if(i==1) //to verify the log id and pwd
			 {      
				 // JOptionPane.showMessageDialog(null,"Correct","Login Error", JOptionPane.INFORMATION_MESSAGE);
	                	   System.out.println("password changed");
	                	   cpbean.setValid(true);
	               //System.out.println(" <html><body><form action=HomePage.jsp></form></body></html>");
	                	   
	              //  new LoginServlet();        
	               //dispose();                    
	          }
	          else
	          {
	           	  System.out.println("pass not changed ");
	           	 // new NewServlet();
	             // JOptionPane.showMessageDialog(null,"Invalid Username or Password","Login Error", JOptionPane.ERROR_MESSAGE);
	          }      
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
		}  
		
		return cpbean;
	}
	

	// add names in list
/*	public List readMasterNames() 
	{
		Statement st = null;
		ResultSet rs = null;
		List li1 = new ArrayList(); // list
		Connection con=dbc.getNewConnection(); // connection
		
		// add customer master names in list
		String sql= "select cname from CustomerMaster";
		try 
		{
			st = con.createStatement();
			rs = st.executeQuery(sql);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
			
		if (rs != null) 
		{
			try 
			{
				while (rs.next()) 
				{
					String c_name = rs.getString("cname");
					li1.add(c_name);
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		} 
		
		// add supplier master names in list
		String sql1= "select sname from SupplierMaster";
		try 
		{
			st = con.createStatement();
			rs = st.executeQuery(sql1);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
			
		if (rs != null) 
		{
			try 
			{
				while (rs.next()) 
				{
					String s_name = rs.getString("sname");
					li1.add(s_name);
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		} 
	
		// add general ledger names in list
		String sql2= "select gname from GeneralLedger";	
		try 
		{
			st = con.createStatement();
			rs = st.executeQuery(sql2);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
			
		if (rs != null) 
		{
			try 
			{
				while (rs.next()) 
				{
					String g_name = rs.getString("gname");
					li1.add(g_name);
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		} 
		return li1;			
	}
	
	// add bank names in list
	public List readBankNames() 
	{
		Statement st = null;
		ResultSet rs = null;
		List li2 = new ArrayList(); // list
		Connection con=dbc.getNewConnection(); // connection
		
		// add customer master names in list
		String sqlb= "select bname from BankMaster";
		try 
		{
			st = con.createStatement();
			rs = st.executeQuery(sqlb);
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
			
		if (rs != null) 
		{
			try 
			{
				while (rs.next()) 
				{
					String b_name = rs.getString("bname");
					li2.add(b_name);
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		} 
		
		return li2;			
	}
*/
	
	public static UserBean login(UserBean bean) 
	{ 
		//preparing some objects for connection
		
		Connection con=dbc.getNewConnection();
		Statement stmt = null;
		String username = bean.getUsername(); 
		String password = bean.getPassword(); 
		
		int i=0;
        
		try
		{
			Statement st = con.createStatement();
            ResultSet     rs = st.executeQuery("Select panno,pass from register where panno='"+username+"' and pass='"+password+"'; ");
                
            //to fetch login details entered by the user on the login form 
            
		   while(rs.next()) //to fetch login details from database
           {   
		      username=rs.getString(1);
		      password=rs.getString(2);
		      //System.out.println(loginid);
		      //System.out.println(passwd);
               i=1;
            }
                                                
           if(i==1) //to verify the log id and pwd
		   { 
        	   // JOptionPane.showMessageDialog(null,"Correct","Login Error", JOptionPane.INFORMATION_MESSAGE);
               System.out.println("success hi");
               bean.setValid(true);
               //System.out.println(" <html><body><form action=HomePage.jsp></form></body></html>");
                	   
               //  new LoginServlet();        
               //dispose();
                        
            }     
           else
           {
               System.out.println("Unsuccessful ");
               // new NewServlet();
              // JOptionPane.showMessageDialog(null,"Invalid Username or Password","Login Error", JOptionPane.ERROR_MESSAGE);
            }                                  
         }//try
         catch(Exception e)
         {
            System.out.println(e);
         }       
		return bean; 
	}
	
} // DBManipulation
